<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width">
<meta name="theme-color" content="#222"><meta name="generator" content="Hexo 5.4.2">

  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">



<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css" integrity="sha256-HtsXJanqjKTc8vVQjO4YMhiqFoXkfBsjBWcX91T1jr8=" crossorigin="anonymous">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/3.1.1/animate.min.css" integrity="sha256-PR7ttpcvz8qrF57fur/yAx1qXMFJeJFiA6pSzWi0OIE=" crossorigin="anonymous">

<script class="next-config" data-name="main" type="application/json">{"hostname":"xiaoqingming18.github.io","root":"/","images":"/images","scheme":"Muse","darkmode":false,"version":"8.15.1","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12},"copycode":{"enable":false,"style":null},"bookmark":{"enable":false,"color":"#222","save":"auto"},"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"stickytabs":false,"motion":{"enable":true,"async":false,"transition":{"menu_item":"fadeInDown","post_block":"fadeIn","post_header":"fadeInDown","post_body":"fadeInDown","coll_header":"fadeInLeft","sidebar":"fadeInUp"}},"prism":false,"i18n":{"placeholder":"搜索...","empty":"没有找到任何搜索结果：${query}","hits_time":"找到 ${hits} 个搜索结果（用时 ${time} 毫秒）","hits":"找到 ${hits} 个搜索结果"}}</script><script src="/js/config.js"></script>

    <meta name="description" content="JDBC及其配置JDBC介绍JDBC（Java Database Connectivity）是Java编程语言的一种标准API，用于与数据库进行交互。它提供了一组用于执行SQL语句、访问和处理数据库的方法和接口。 JDBC允许Java应用程序通过标准的数据库操作语言（如SQL）与各种关系型数据库进行通信，包括MySQL、Oracle、SQL Server等。它提供了一种统一的编程接口，使得开发人员">
<meta property="og:type" content="article">
<meta property="og:title" content="Java：数据库编程">
<meta property="og:url" content="http://xiaoqingming18.github.io/2023/06/01/Java%EF%BC%9A%E6%95%B0%E6%8D%AE%E5%BA%93%E7%BC%96%E7%A8%8B/index.html">
<meta property="og:site_name" content="鸣蜩十七">
<meta property="og:description" content="JDBC及其配置JDBC介绍JDBC（Java Database Connectivity）是Java编程语言的一种标准API，用于与数据库进行交互。它提供了一组用于执行SQL语句、访问和处理数据库的方法和接口。 JDBC允许Java应用程序通过标准的数据库操作语言（如SQL）与各种关系型数据库进行通信，包括MySQL、Oracle、SQL Server等。它提供了一种统一的编程接口，使得开发人员">
<meta property="og:locale" content="zh_CN">
<meta property="article:published_time" content="2023-06-01T10:46:33.000Z">
<meta property="article:modified_time" content="2023-06-02T01:54:27.655Z">
<meta property="article:author" content="鸣蜩十七">
<meta property="article:tag" content="-Java基础 -数据库编程 -Java数据库编程">
<meta name="twitter:card" content="summary">


<link rel="canonical" href="http://xiaoqingming18.github.io/2023/06/01/Java%EF%BC%9A%E6%95%B0%E6%8D%AE%E5%BA%93%E7%BC%96%E7%A8%8B/">



<script class="next-config" data-name="page" type="application/json">{"sidebar":"","isHome":false,"isPost":true,"lang":"zh-CN","comments":true,"permalink":"http://xiaoqingming18.github.io/2023/06/01/Java%EF%BC%9A%E6%95%B0%E6%8D%AE%E5%BA%93%E7%BC%96%E7%A8%8B/","path":"2023/06/01/Java：数据库编程/","title":"Java：数据库编程"}</script>

<script class="next-config" data-name="calendar" type="application/json">""</script>
<title>Java：数据库编程 | 鸣蜩十七</title>
  








  <noscript>
    <link rel="stylesheet" href="/css/noscript.css">
  </noscript>

<style>.github-emoji { position: relative; display: inline-block; width: 1.2em; min-height: 1.2em; overflow: hidden; vertical-align: top; color: transparent; }  .github-emoji > span { position: relative; z-index: 10; }  .github-emoji img, .github-emoji .fancybox { margin: 0 !important; padding: 0 !important; border: none !important; outline: none !important; text-decoration: none !important; user-select: none !important; cursor: auto !important; }  .github-emoji img { height: 1.2em !important; width: 1.2em !important; position: absolute !important; left: 50% !important; top: 50% !important; transform: translate(-50%, -50%) !important; user-select: none !important; cursor: auto !important; } .github-emoji-fallback { color: inherit; } .github-emoji-fallback img { opacity: 0 !important; }</style>
</head>

<body itemscope itemtype="http://schema.org/WebPage" class="use-motion">
  <div class="headband"></div>

  <main class="main">
    <div class="column">
      <header class="header" itemscope itemtype="http://schema.org/WPHeader"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏" role="button">
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <i class="logo-line"></i>
      <p class="site-title">鸣蜩十七</p>
      <i class="logo-line"></i>
    </a>
      <p class="site-subtitle" itemprop="description">鸣蜩十七是和女朋友谈恋爱的日子</p>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger" aria-label="搜索" role="button">
    </div>
  </div>
</div>



<nav class="site-nav">
  <ul class="main-menu menu"><li class="menu-item menu-item-home"><a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a></li><li class="menu-item menu-item-about"><a href="/about/" rel="section"><i class="fa fa-user fa-fw"></i>关于</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a></li><li class="menu-item menu-item-categories"><a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a></li><li class="menu-item menu-item-schedule"><a href="/schedule/" rel="section"><i class="fa fa-calendar fa-fw"></i>日程表</a></li><li class="menu-item menu-item-sitemap"><a href="/sitemap.xml" rel="section"><i class="fa fa-sitemap fa-fw"></i>站点地图</a></li><li class="menu-item menu-item-commonweal"><a href="/404/" rel="section"><i class="fa fa-heartbeat fa-fw"></i>公益 404</a></li>
  </ul>
</nav>




</header>
        
  
  <aside class="sidebar">

    <div class="sidebar-inner sidebar-nav-active sidebar-toc-active">
      <ul class="sidebar-nav">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <div class="sidebar-panel-container">
        <!--noindex-->
        <div class="post-toc-wrap sidebar-panel">
            <div class="post-toc animated"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#JDBC%E5%8F%8A%E5%85%B6%E9%85%8D%E7%BD%AE"><span class="nav-text">JDBC及其配置</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#JDBC%E4%BB%8B%E7%BB%8D"><span class="nav-text">JDBC介绍</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E8%BF%9E%E6%8E%A5%E5%88%B0%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="nav-text">连接到数据库</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%8A%A0%E8%BD%BD%E6%95%B0%E6%8D%AE%E5%BA%93%E9%A9%B1%E5%8A%A8%E7%A8%8B%E5%BA%8F"><span class="nav-text">加载数据库驱动程序</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93URL"><span class="nav-text">数据库URL</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#DriverManager-connection"><span class="nav-text">DriverManager.connection</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="nav-text">连接数据库</span></a></li></ol></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E4%BD%BF%E7%94%A8JDBC%E8%AF%AD%E5%8F%A5"><span class="nav-text">使用JDBC语句</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%89%A7%E8%A1%8CSQL%E8%AF%AD%E5%8F%A5"><span class="nav-text">执行SQL语句</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#Statement%E5%AF%B9%E8%B1%A1"><span class="nav-text">Statement对象</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E9%80%9A%E8%BF%87Statement%E5%AF%B9%E8%B1%A1%E6%89%A7%E8%A1%8C%E9%9D%99%E6%80%81SQL%E8%AF%AD%E5%8F%A5"><span class="nav-text">通过Statement对象执行静态SQL语句</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E9%80%9A%E8%BF%87ReaultSet%E6%9F%A5%E7%9C%8B%E6%9F%A5%E8%AF%A2%E7%BB%93%E6%9E%9C"><span class="nav-text">通过ReaultSet查看查询结果</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#ResultSet%E8%AE%BF%E9%97%AE%E5%99%A8"><span class="nav-text">ResultSet访问器</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E7%AE%A1%E7%90%86%E8%BF%9E%E6%8E%A5%E3%80%81%E8%AF%AD%E5%8F%A5%E5%92%8C%E7%BB%93%E6%9E%9C%E9%9B%86"><span class="nav-text">管理连接、语句和结果集</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#SQL%E5%BC%82%E5%B8%B8"><span class="nav-text">SQL异常</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E6%9F%A5%E8%AF%A2%E6%93%8D%E4%BD%9C%E8%AF%A6%E8%A7%A3"><span class="nav-text">查询操作详解</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E9%A2%84%E5%A4%87%E8%AF%AD%E5%8F%A5"><span class="nav-text">预备语句</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E9%A2%84%E7%BC%96%E8%AF%91SQL%E8%AF%AD%E5%8F%A5"><span class="nav-text">预编译SQL语句</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%88%9B%E5%BB%BA%E9%A2%84%E5%A4%87%E8%AF%AD%E5%8F%A5"><span class="nav-text">创建预备语句</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%BB%91%E5%AE%9A%E5%8F%82%E6%95%B0"><span class="nav-text">绑定参数</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%89%A7%E8%A1%8C%E9%A2%84%E5%A4%87%E8%AF%AD%E5%8F%A5"><span class="nav-text">执行预备语句</span></a></li></ol></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E8%AF%BB%E5%86%99LOB"><span class="nav-text">读写LOB</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E8%AF%BBLOB"><span class="nav-text">读LOB</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%86%99LOB"><span class="nav-text">写LOB</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E5%8F%AF%E6%BB%9A%E5%8A%A8%E5%92%8C%E5%8F%AF%E6%9B%B4%E6%96%B0%E7%9A%84%E7%BB%93%E6%9E%9C%E9%9B%86"><span class="nav-text">可滚动和可更新的结果集</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%8F%AF%E6%BB%9A%E5%8A%A8%E7%BB%93%E6%9E%9C%E9%9B%86"><span class="nav-text">可滚动结果集</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%8F%AF%E6%9B%B4%E6%96%B0%E7%BB%93%E6%9E%9C%E9%9B%86"><span class="nav-text">可更新结果集</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E4%BA%8B%E5%8A%A1"><span class="nav-text">事务</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E7%94%A8JDBC%E5%AF%B9%E4%BA%8B%E5%8A%A1%E7%BC%96%E7%A8%8B"><span class="nav-text">用JDBC对事务编程</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%89%B9%E9%87%8F%E6%9B%B4%E6%96%B0"><span class="nav-text">批量更新</span></a></li></ol></li></ol></div>
        </div>
        <!--/noindex-->

        <div class="site-overview-wrap sidebar-panel">
          <div class="site-author animated" itemprop="author" itemscope itemtype="http://schema.org/Person">
  <p class="site-author-name" itemprop="name">鸣蜩十七</p>
  <div class="site-description" itemprop="description">记录我的学习、生活和恋爱日常</div>
</div>
<div class="site-state-wrap animated">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
        <a href="/archives/">
          <span class="site-state-item-count">43</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
          <a href="/categories/">
        <span class="site-state-item-count">8</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
          <a href="/tags/">
        <span class="site-state-item-count">37</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>

        </div>
      </div>
    </div>

    
  </aside>


    </div>

    <div class="main-inner post posts-expand">


  


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="http://xiaoqingming18.github.io/2023/06/01/Java%EF%BC%9A%E6%95%B0%E6%8D%AE%E5%BA%93%E7%BC%96%E7%A8%8B/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="鸣蜩十七">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="鸣蜩十七">
      <meta itemprop="description" content="记录我的学习、生活和恋爱日常">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="Java：数据库编程 | 鸣蜩十七">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          Java：数据库编程
        </h1>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2023-06-01 18:46:33" itemprop="dateCreated datePublished" datetime="2023-06-01T18:46:33+08:00">2023-06-01</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2023-06-02 09:54:27" itemprop="dateModified" datetime="2023-06-02T09:54:27+08:00">2023-06-02</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E5%90%8E%E7%AB%AF/" itemprop="url" rel="index"><span itemprop="name">-后端</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
        <h1 id="JDBC及其配置"><a href="#JDBC及其配置" class="headerlink" title="JDBC及其配置"></a>JDBC及其配置</h1><h2 id="JDBC介绍"><a href="#JDBC介绍" class="headerlink" title="JDBC介绍"></a>JDBC介绍</h2><p>JDBC（Java Database Connectivity）是Java编程语言的一种标准API，用于与数据库进行交互。它提供了一组用于执行SQL语句、访问和处理数据库的方法和接口。</p>
<p>JDBC允许Java应用程序通过标准的数据库操作语言（如SQL）与各种关系型数据库进行通信，包括MySQL、Oracle、SQL Server等。它提供了一种统一的编程接口，使得开发人员可以编写与数据库无关的代码，从而实现数据库的访问和操作。</p>
<p>以下是一些JDBC的关键概念和组件：</p>
<ol>
<li>DriverManager：JDBC的入口点，用于获取数据库连接。</li>
<li>Connection：表示与数据库的连接，用于创建和执行SQL语句。</li>
<li>Statement和PreparedStatement：用于执行SQL语句的接口，可以执行查询、插入、更新等操作。</li>
<li>ResultSet：表示查询结果集的接口，用于遍历和访问查询结果。</li>
<li>Driver：JDBC驱动程序，实现了JDBC接口，负责与特定数据库进行通信。</li>
</ol>
<p>JDBC的工作流程通常包括以下步骤：</p>
<ol>
<li>加载数据库驱动程序：使用Class.forName()方法加载特定数据库的驱动程序，例如”com.mysql.jdbc.Driver”。</li>
<li>建立数据库连接：通过DriverManager.getConnection()方法获取与数据库的连接，需要提供数据库的URL、用户名和密码。</li>
<li>创建Statement或PreparedStatement：通过Connection对象的createStatement()或prepareStatement()方法创建Statement或PreparedStatement对象，用于执行SQL语句。</li>
<li>执行SQL语句：使用Statement或PreparedStatement对象的executeQuery()、executeUpdate()等方法执行SQL语句，获取查询结果或影响的行数。</li>
<li>处理查询结果：如果执行的是查询语句，可以使用ResultSet对象遍历和访问查询结果。</li>
<li>关闭资源：在不再需要时，关闭ResultSet、Statement、Connection等对象，释放数据库资源。</li>
</ol>
<h2 id="连接到数据库"><a href="#连接到数据库" class="headerlink" title="连接到数据库"></a>连接到数据库</h2><p><code>JDBC</code>连接到数据库需要提供有关数据库的三个参数：数据库URL、用户名和密码。</p>
<h3 id="加载数据库驱动程序"><a href="#加载数据库驱动程序" class="headerlink" title="加载数据库驱动程序"></a>加载数据库驱动程序</h3><p>使用Class.forName()方法加载特定数据库的驱动程序，例如加载Sql Server数据库驱动程序：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Class.forName(<span class="string">"com.microsoft.sqlserver.jdbc.SQLServerDriver"</span>);</span><br></pre></td></tr></tbody></table></figure>
<h3 id="数据库URL"><a href="#数据库URL" class="headerlink" title="数据库URL"></a>数据库URL</h3><p>数据库URL的语法格式可以根据不同的数据库类型而有所差异，下面是Sql Server数据库的URL格式：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">jdbc:sqlserver:<span class="comment">//hostname:port;databaseName=database</span></span><br><span class="line"><span class="comment">// hostname表示数据库服务器的主机名或IP地址</span></span><br><span class="line"><span class="comment">// port表示数据库服务器的端口号（默认为1433）</span></span><br><span class="line"><span class="comment">// database表示要连接的数据库名称</span></span><br></pre></td></tr></tbody></table></figure>
<p>有时根据使用的数据库管理系统不同，还需要在数据库URL中加入一些参数，比如使用最新版Sql Server就需要在数据库URL中加入三个参数，<code>integratedSecurity=false</code>、<code>encrypt=false</code>和<code>trustServerCertificate=false</code>。</p>
<p>所以，使用Sql Server，最终的数据库URL如下所示：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">jdbc:sqlserver:<span class="comment">//hostname:port;databaseName=database;integratedSecurity=false;encrypt=false;trustServerCertificate=false</span></span><br></pre></td></tr></tbody></table></figure>
<h3 id="DriverManager-connection"><a href="#DriverManager-connection" class="headerlink" title="DriverManager.connection"></a>DriverManager.connection</h3><p><code>DriverManager.connection(dbURL,username,password)</code>方法用来连接到数据库，接收三个参数，数据库URL、用户名和密码。返回一个<code>Connection</code>对象，它表示应用程序与数据库之间的通信通道，用于执行SQL语句、提交事务和管理数据库连接。</p>
<h3 id="连接数据库"><a href="#连接数据库" class="headerlink" title="连接数据库"></a>连接数据库</h3><p>以Sql Server为例，像下面这样连接到数据库：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 指定要连接的数据库名称</span></span><br><span class="line"><span class="type">String</span> <span class="variable">dateBaseName</span> <span class="operator">=</span> <span class="string">"XSCJ"</span>;</span><br><span class="line"><span class="comment">// 指定数据库URL</span></span><br><span class="line"><span class="type">String</span> <span class="variable">dbURL</span> <span class="operator">=</span> <span class="string">"jdbc:sqlserver://localhost:1433;databaseName="</span> + dateBaseName+<span class="string">";integratedSecurity=false;"</span>+<span class="string">"encrypt=false;trustServerCertificate=false"</span>;</span><br><span class="line"><span class="comment">// 指定用户名和密码</span></span><br><span class="line"><span class="type">String</span> <span class="variable">username</span> <span class="operator">=</span> <span class="string">"username"</span>;</span><br><span class="line"><span class="type">String</span> <span class="variable">password</span> <span class="operator">=</span> <span class="string">"password"</span>;</span><br><span class="line"><span class="comment">// 连接到数据库</span></span><br><span class="line"><span class="type">Connection</span> <span class="variable">connection</span> <span class="operator">=</span> DriverManager.getConnection(dbURL,username,password);</span><br></pre></td></tr></tbody></table></figure>
<h1 id="使用JDBC语句"><a href="#使用JDBC语句" class="headerlink" title="使用JDBC语句"></a>使用JDBC语句</h1><p>JDBC语句的主要作用是与数据库进行交互，执行数据库操作。通过JDBC语句，可以实现数据查询、数据更新、事务管理和数据库管理等功能。</p>
<h2 id="执行SQL语句"><a href="#执行SQL语句" class="headerlink" title="执行SQL语句"></a>执行SQL语句</h2><p>执行SQL语句需要一个<code>Statement</code>对象，所以我们需要先了解<code>Statement</code>对象。</p>
<h3 id="Statement对象"><a href="#Statement对象" class="headerlink" title="Statement对象"></a>Statement对象</h3><p><code>Statement</code>对象是Java JDBC中用于执行SQL语句的接口之一。它用于发送SQL语句到数据库并执行操作，<strong>通常用于执行静态SQL语句</strong>，即在编译时就确定的SQL语句。</p>
<p>可以通过连接数据库时返回的<code>Connection</code>对象的<code>createStatement()</code>方法创建<code>Statement</code>对象：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">Statement</span> <span class="variable">statement</span> <span class="operator">=</span> connection.createStatement();</span><br></pre></td></tr></tbody></table></figure>
<h3 id="通过Statement对象执行静态SQL语句"><a href="#通过Statement对象执行静态SQL语句" class="headerlink" title="通过Statement对象执行静态SQL语句"></a>通过Statement对象执行静态SQL语句</h3><p>首先，把要执行的SQL语句放入一个字符串，比如：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">String</span> <span class="variable">command</span> <span class="operator">=</span> <span class="string">"UPDATE *"</span></span><br><span class="line">    +<span class="string">" FROM Student"</span>;</span><br></pre></td></tr></tbody></table></figure>
<p>要注意的是，如果SQL语句换行，<strong>每一行的开头需要空一格，否则拼接后的SQL语句会缺少空格</strong>。</p>
<p>然后调用<code>Statement</code>对象的三个方法来执行SQL语句，这三个方法分别是：</p>
<ul>
<li>execute()：最通用的执行SQL语句的方法，可以执行任意类型的SQL语句，返回结果为布尔值，表示是否执行了查询语句。</li>
<li>executeQuery()：用于执行SELECT查询语句，返回查询结果的ResultSet对象。</li>
<li>executeUpdate()：用于执行更新语句，返回受影响的行数。</li>
</ul>
<h3 id="通过ReaultSet查看查询结果"><a href="#通过ReaultSet查看查询结果" class="headerlink" title="通过ReaultSet查看查询结果"></a>通过ReaultSet查看查询结果</h3><p><code>ResultSet</code>对象也称结果集。可以使用 ResultSet 对象来遍历和访问查询结果中的数据。<strong><code>ResultSet</code>对象通过调用<code>executeQuery()</code>方法执行查询操作来返回</strong>:</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">Result</span> <span class="variable">result</span> <span class="operator">=</span> statement.executeQuery(command);</span><br></pre></td></tr></tbody></table></figure>
<p>分析结果集可以使用与下面类似的循环语句：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">while</span>(result.next()) {</span><br><span class="line">    <span class="comment">// do something</span></span><br><span class="line">}</span><br></pre></td></tr></tbody></table></figure>
<p><code>ResultSet</code>的迭代器初始化在第一行之前，必须调用<code>next</code>方法将迭代器移动到第一行，另外，它没有<code>hasNext</code>方法，需要不断的调用<code>next</code>方法，知道该方法返回<code>false</code>停止。</p>
<p>结果集中行的顺序是任意排列的，除非使用<code>ORDER BY</code>子句指定行的顺序。</p>
<h3 id="ResultSet访问器"><a href="#ResultSet访问器" class="headerlink" title="ResultSet访问器"></a>ResultSet访问器</h3><p>可以通过<code>ResultSet</code>对象提供的各种<strong>访问器</strong>来获取一行中每一列的内容，并且不同的数据类型有不同的访问器，比如<code>getString</code>和<code>getDouble</code>。</p>
<p>每个访问器接受两种类型的参数，一类是数值，一类是字符。当使用数值作为参数时，访问器返回对应第几列的数据。当使用字符串作为参数时，访问器返回对应同名列的数据。</p>
<p>例如，调用<code>result.getString("Sname")</code>会返回列名为<code>Sname</code>的内容。</p>
<h2 id="管理连接、语句和结果集"><a href="#管理连接、语句和结果集" class="headerlink" title="管理连接、语句和结果集"></a>管理连接、语句和结果集</h2><p>每个<code>Connection</code>对象可以创建多个<code>Statement</code>对象，同一个<code>Statement</code>对象可以执行多次不相关的命令和查询。但是，<strong>一个<code>Statement</code>对象只能有一个打开的结果集</strong>。如果需要同时执行多个查询操作，且同时需要分析查询结果，就需要创建多个<code>Statement</code>对象。</p>
<p>使用完<code>Connection</code>、<code>Statement</code>和<code>Connection</code>对象后应立即调用<code>close</code>方法关闭它们，这些对象都使用了规模较大的数据结构，会占用数据库服务器上的有限资源。</p>
<h1 id="SQL异常"><a href="#SQL异常" class="headerlink" title="SQL异常"></a>SQL异常</h1><p>等待补充</p>
<h1 id="查询操作详解"><a href="#查询操作详解" class="headerlink" title="查询操作详解"></a>查询操作详解</h1><h2 id="预备语句"><a href="#预备语句" class="headerlink" title="预备语句"></a>预备语句</h2><p>上面介绍的查询都是基于提前编写好的SQL语句，即静态SQL语句的查询。而下面要介绍的<strong>预备语句</strong>用于执行预编译的 SQL 语句。</p>
<p>预备语句的工作即优势如下：</p>
<ol>
<li>预编译：在创建预备语句时，数据库系统会对 SQL 语句进行预编译，并将其存储在数据库服务器中。这个过程只发生一次。</li>
<li>参数化查询：预备语句允许使用参数化查询，即将查询中的参数位置用占位符（如<code>?</code>）代替。这些占位符可以在执行预备语句之前动态地绑定具体的值。参数化查询可以防止常见的安全漏洞，如 SQL 注入攻击。</li>
<li>提高性能：由于预备语句已经预先编译，数据库系统会将其优化并存储执行计划。当多次执行相同的预备语句时，只需要发送参数值给数据库服务器，而不必重新编译和优化查询，这可以显著提高执行效率。</li>
<li>简化代码：使用预备语句可以简化代码编写过程，避免手动拼接 SQL 字符串带来的麻烦。预备语句提供了一种更结构化的方式来处理 SQL 查询。</li>
</ol>
<h3 id="预编译SQL语句"><a href="#预编译SQL语句" class="headerlink" title="预编译SQL语句"></a>预编译SQL语句</h3><p>在普通的SQL语句中，将查询条件中的每个宿主变量用问号代替，例如：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">String</span> <span class="variable">command</span> <span class="operator">=</span> <span class="string">"SELECT Sno,Sname"</span></span><br><span class="line">    +<span class="string">" FROM Student"</span></span><br><span class="line">    +<span class="string">" WHERE Sno = ?"</span></span><br></pre></td></tr></tbody></table></figure>
<h3 id="创建预备语句"><a href="#创建预备语句" class="headerlink" title="创建预备语句"></a>创建预备语句</h3><p>创建预备语句，就是创建<code>PreparedStatement</code>对象。</p>
<p><code>PreparedStatement</code>对象是在 Java 数据库编程中用于执行预编译 SQL 语句的特殊对象，可以通过连接数据库时返回的<code>Connection</code>对象的<code>prepareStatement(预编译SQL语句字符串)</code>方法创建:</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">PreparedStatement</span> <span class="variable">preparedStatement</span> <span class="operator">=</span> connection.prepareStatement(command);</span><br></pre></td></tr></tbody></table></figure>
<h3 id="绑定参数"><a href="#绑定参数" class="headerlink" title="绑定参数"></a>绑定参数</h3><p>在执行预备语句之前需要先绑定参数，使用预备语句<code>PreparedStatement</code>对象的<code>setXXX(int n,XXX x)</code>方法为每个占位符设置具体的值，其中 <code>XXX</code> 是对应参数的数据类型，参数<code>n</code>对应第几个占位符，参数<code>x</code>表示用于替代的内容。例如：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">preparedStatement.setString(<span class="number">1</span>,<span class="string">"21044028"</span>);</span><br></pre></td></tr></tbody></table></figure>
<p>如果想要重用已经执行过的预备查询语句，只需要重新绑定参数即可。</p>
<h3 id="执行预备语句"><a href="#执行预备语句" class="headerlink" title="执行预备语句"></a>执行预备语句</h3><p>执行预备语句的方法与之前说的类似，也是返回一个结果集：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">ResultSet</span> <span class="variable">result</span> <span class="operator">=</span> preparedStatement.executeQuery();</span><br></pre></td></tr></tbody></table></figure>
<h1 id="读写LOB"><a href="#读写LOB" class="headerlink" title="读写LOB"></a>读写LOB</h1><h2 id="读LOB"><a href="#读LOB" class="headerlink" title="读LOB"></a>读LOB</h2><p>许多数据库还可以存储大对象，例如图片或其他数据。在SQL中，二进制大对象称为<strong>BLOB</strong>，字符型大对象称为<strong>CLOB</strong>。</p>
<p>要读取LOB，需要执行SELECT语句，然后再<code>ResultSet</code>结果集上调用<code>getBlob(int index)</code>或<code>getClob(int index)</code>方法，这样就可以获得指定列的<code>Blob</code>或<code>Clob</code>类型的对象。</p>
<p>要从<code>Blob</code>对象中获取二进制数据，可以调用<code>getBytes()</code>或<code>getBinaryStream()</code>方法。例如，有一张保存图书封面的表，就可以像下面这样获取一张图像：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">PreparedStatement</span> <span class="variable">preparedStatement</span> <span class="operator">=</span> connection.prepareStatement(<span class="string">"SELECT Cover FROM BookCovers WHERE ISBN = ?"</span>);</span><br><span class="line">preparedStatement.setString(<span class="number">1</span>,<span class="string">"59854126445"</span>);</span><br><span class="line"><span class="keyword">try</span>(<span class="type">ResultSet</span> <span class="variable">result</span> <span class="operator">=</span> preparedStatement.executeQuery())</span><br><span class="line">{</span><br><span class="line">    <span class="keyword">if</span>(result.next()) {</span><br><span class="line">        <span class="type">Blob</span> <span class="variable">coverBlob</span> <span class="operator">=</span> result.getBlob(<span class="number">1</span>);</span><br><span class="line">        <span class="type">Image</span> <span class="variable">coverImage</span> <span class="operator">=</span> ImageIO.read(coverBlob.getBinaryStream());</span><br><span class="line">    }</span><br><span class="line">}</span><br></pre></td></tr></tbody></table></figure>
<p>类似的，如果获取了<code>Clob</code>对象，就可以通过调用<code>ResultSet</code>结果集的<code>getSubString</code>或<code>getCharacterStream</code>方法来获取其中的字符数据。这两个方法的详细说明如下：</p>
<ol>
<li><code>getSubString(long columnIndex, long length)</code> ：用于从结果集中获取指定列的数据，并将其作为一个子字符串返回，适用于从数据库中检索字符串类型（如VARCHAR、CHAR等）的列的数据。<ul>
<li><code>columnIndex</code>：表示要获取数据的列的索引（从1开始），或者可以使用列的名称。</li>
<li><code>length</code>：表示要获取的子字符串的长度。</li>
</ul>
</li>
<li><code>getCharacterStream(int columnIndex)</code> ：用于从结果集中获取指定列的数据，并将其作为一个字符流（<code>Reader</code> 对象）返回,适用于从数据库中检索长文本类型（如CLOB）的列的数据。<ul>
<li><code>columnIndex</code>：表示要获取数据的列的索引（从1开始），或者可以使用列的名称。</li>
</ul>
</li>
</ol>
<h2 id="写LOB"><a href="#写LOB" class="headerlink" title="写LOB"></a>写LOB</h2><p>要将LOB置入数据库中，需要在<code>Connection</code>对象上调用<code>createBlob</code>或<code>createClob</code>，然后获取一个用于该LOB的输出流或写出器，写出数据，并将该对象存储到数据库中。下面用一个例子说明如何向数据库中写入图片。</p>
<p>假设现在有一张StudentImage表，现在需要向其中插入某个学生的图片，可以像下面这样做：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 将指定的文件放入输入流中</span></span><br><span class="line"><span class="type">BufferedInputStream</span> <span class="variable">photoInputStream</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">BufferedInputStream</span>(<span class="keyword">new</span> <span class="title class_">FileInputStream</span>(file))</span><br><span class="line"><span class="comment">// 创建预备语句，也就是PreparedStatement对象</span></span><br><span class="line"><span class="type">PreparedStatement</span> <span class="variable">preparedStatement</span> <span class="operator">=</span> connection.prepareStatement(<span class="string">"INSERT INTO StudentImage (student_id, photo) VALUES (?, ?)"</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">// 设置参数</span></span><br><span class="line">preparedStatement.setInt(<span class="number">1</span>, studentId);  <span class="comment">// studentId 是学生的ID</span></span><br><span class="line">preparedStatement.setBinaryStream(<span class="number">2</span>, photoInputStream);  </span><br><span class="line"><span class="comment">// 开始插入</span></span><br><span class="line"><span class="type">int</span> <span class="variable">result</span> <span class="operator">=</span> preparedStatement.executeUpdate();</span><br><span class="line"><span class="keyword">if</span>(result &gt; <span class="number">0</span>) {</span><br><span class="line">    System.out.println(<span class="string">"插入成功"</span>);</span><br><span class="line">}<span class="keyword">else</span> {</span><br><span class="line">    System.out.println(<span class="string">"插入失败"</span>);</span><br><span class="line">}</span><br></pre></td></tr></tbody></table></figure>
<h1 id="可滚动和可更新的结果集"><a href="#可滚动和可更新的结果集" class="headerlink" title="可滚动和可更新的结果集"></a>可滚动和可更新的结果集</h1><p>默认的结果集<code>ResultSet</code>使用<code>next</code>方法迭代遍历结果集中的所有行。可滚动和可更新的结果集是 Java JDBC 中提供的两种扩展功能，用于对查询结果进行更灵活的操作。</p>
<p><code>ResultSet</code>接口提供了两个参数，即结果集的 <code>Type</code>（类型）和 <code>Concurrency</code>（并发性），用于指定结果集的的特性和并发访问方式。</p>
<ol>
<li><code>ResultSet</code> 的类型（Type）：<ul>
<li><code>TYPE_FORWARD_ONLY</code>：默认类型，表示结果集只能向前（从第一行到最后一行）进行遍历，不支持向后导航或随机访问。</li>
<li><code>TYPE_SCROLL_INSENSITIVE</code>：表示结果集可以滚动，允许向前和向后导航，但对结果集所做的更改不会反映到数据库中。</li>
<li><code>TYPE_SCROLL_SENSITIVE</code>：与 <code>TYPE_SCROLL_INSENSITIVE</code> 类似，允许向前和向后导航，但对结果集所做的更改会反映到数据库中，可能会影响其他同时访问该数据的用户。</li>
</ul>
</li>
<li><code>ResultSet</code> 的并发性（Concurrency）：<ul>
<li><code>CONCUR_READ_ONLY</code>：默认并发性，表示结果集是只读的，不能对其进行更新。</li>
<li><code>CONCUR_UPDATABLE</code>：表示结果集是可更新的，允许对结果集中的数据进行修改、插入和删除操作。</li>
</ul>
</li>
</ol>
<p>默认情况下，结果集是不可滚动和不可更新的。为了从查询中获取可滚动和可更新的结果集，必须使用下面的方法得到一个不同的<code>Statement</code>或<code>PreparedStatement</code>对象：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">Statement</span> <span class="variable">statement</span> <span class="operator">=</span> connection.createStatement(type,concurrency);</span><br><span class="line"><span class="type">PreparedStatement</span> <span class="variable">preparedStatement</span> <span class="operator">=</span> connection.prepareStatement(command,type,concurrency);</span><br></pre></td></tr></tbody></table></figure>
<h2 id="可滚动结果集"><a href="#可滚动结果集" class="headerlink" title="可滚动结果集"></a>可滚动结果集</h2><p>可以像下面这样先获得一个可滚动结果集：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">Statement</span> <span class="variable">statement</span> <span class="operator">=</span> connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);</span><br></pre></td></tr></tbody></table></figure>
<p>可滚动结果集允许在结果集中自由地向前和向后导航，并能够定位到指定的行。</p>
<p>可滚动结果集有一个游标，用以指示当前位置。在结果集上滚动可以使用下面的语句向后滚动（即向上一行滚动）：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">if</span>(result.previous())···；</span><br></pre></td></tr></tbody></table></figure>
<p>如果游标位于一个实际的行上，该方法返回<code>true</code>，如果游标位于第一行之前，返回<code>false</code>。</p>
<p>也可以使用<code>result.relative(int n)</code>方法将游标向前（下一行）或向后（上一行）移动多行，当<code>n</code>为正数时，游标向前（向下一行）移动，当<code>n</code>为负数时，游标向后（向上一行）移动。如果游标移动到第一行之前或最后一行之后，该方法返回<code>false</code>，且不移动游标。如果游标移动到一个实际的行，则返回<code>true</code>。</p>
<p>下面总结一下可滚动结果集用于导航和定位游标的方法：</p>
<ul>
<li><code>previous()</code>：在结果集上向后滚动（即向上一行滚动）。</li>
<li><code>relative(int rows)</code>：将光标相对于当前位置移动指定的行数。</li>
<li><code>beforeFirst()</code>：将光标移动到结果集的开头之前的位置。</li>
<li><code>afterLast()</code>：将光标移动到结果集的末尾之后的位置。</li>
<li><code>first()</code>：将光标移动到结果集的第一行。</li>
<li><code>last()</code>：将光标移动到结果集的最后一行。</li>
<li><code>absolute(int row)</code>：将光标移动到指定行号的位置。</li>
<li><code>getRow</code>：返回当前游标所在行的行号。</li>
</ul>
<p>通过使用可滚动结果集，可以实现在结果集中进行随机访问和定位。</p>
<h2 id="可更新结果集"><a href="#可更新结果集" class="headerlink" title="可更新结果集"></a>可更新结果集</h2><p>可以向下面这样先获取一个可更新结果集：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">Statement</span> <span class="variable">statement</span> <span class="operator">=</span> connection.CreateStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATEABLE);</span><br></pre></td></tr></tbody></table></figure>
<p>默认情况下，结果集是只读的，不能对结果集中的数据进行修改。可更新结果集允许对结果集中的数据进行修改、插入和删除操作。</p>
<p>使用可更新结果集，可以使用以下方法对数据进行操作：</p>
<ul>
<li><code>updateXXX(int columnIndex, value)</code>：更新指定列的数据，其中 <code>XXX</code> 是对应列的数据类型，<code>columnIndex</code> 是列的索引。</li>
<li><code>updateXXX(String columnName, value)</code>：更新指定列的数据，其中<code>columnName</code> 是结果集中的列名，表示要更新的列的名称。</li>
<li><code>insertRow()</code>：在结果集中插入一行新数据。</li>
<li><code>deleteRow()</code>：删除结果集中的当前行。</li>
</ul>
<p>通过使用可更新结果集，可以实现对结果集中的数据进行修改和维护。</p>
<p>下面是一个使用可更新结果集的简单示例：</p>
<p>假设有一个名为 “students” 的表，包含以下列：id（整数类型）、name（字符串类型）和age（整数类型）。</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 创建可更新结果集</span></span><br><span class="line"><span class="type">Statement</span> <span class="variable">stmt</span> <span class="operator">=</span> connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);</span><br><span class="line"><span class="type">ResultSet</span> <span class="variable">rs</span> <span class="operator">=</span> stmt.executeQuery(<span class="string">"SELECT * FROM students"</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">// 向结果集插入新的行</span></span><br><span class="line">rs.moveToInsertRow();</span><br><span class="line">rs.updateInt(<span class="string">"id"</span>, <span class="number">1</span>);</span><br><span class="line">rs.updateString(<span class="string">"name"</span>, <span class="string">"John"</span>);</span><br><span class="line">rs.updateInt(<span class="string">"age"</span>, <span class="number">20</span>);</span><br><span class="line">rs.insertRow();</span><br><span class="line"></span><br><span class="line"><span class="comment">// 移动到新插入的行</span></span><br><span class="line">rs.last();</span><br><span class="line"></span><br><span class="line"><span class="comment">// 更新行数据</span></span><br><span class="line">rs.updateString(<span class="string">"name"</span>, <span class="string">"Jane"</span>);</span><br><span class="line">rs.updateInt(<span class="string">"age"</span>, <span class="number">21</span>);</span><br><span class="line">rs.updateRow();</span><br><span class="line"></span><br><span class="line"><span class="comment">// 删除行数据</span></span><br><span class="line">rs.deleteRow();</span><br><span class="line"></span><br><span class="line"><span class="comment">// 关闭结果集和连接</span></span><br><span class="line">rs.close();</span><br><span class="line">connection.close();</span><br><span class="line"></span><br></pre></td></tr></tbody></table></figure>
<p><code>moveToInsertRow()</code> 方法是 <code>ResultSet</code> 接口中的一个方法，用于将结果集的光标移动到可插入行的位置。</p>
<p>当使用可更新结果集（<code>ResultSet.CONCUR_UPDATABLE</code>）时，可以使用 <code>moveToInsertRow()</code> 方法将结果集的光标移动到特殊的插入行位置。在插入行位置，可以使用 <code>updateXXX()</code> 方法设置每列的值，然后调用 <code>insertRow()</code> 方法将新的行插入到结果集中。</p>
<p>在具体操作过程中，<code>moveToInsertRow()</code> 方法的调用将会改变结果集的状态，将结果集的光标从当前行移动到插入行位置。这意味着在调用 <code>moveToInsertRow()</code> 方法后，你无法直接访问当前行的数据，而是处于一个准备插入新行的状态。</p>
<p>以下是一个示例，展示了如何使用 <code>moveToInsertRow()</code> 方法插入新的行：</p>
<figure class="highlight plaintext"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">javaCopy code// 创建可更新结果集</span><br><span class="line">Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);</span><br><span class="line">ResultSet rs = stmt.executeQuery("SELECT * FROM students");</span><br><span class="line"></span><br><span class="line">// 移动到插入行位置</span><br><span class="line">rs.moveToInsertRow();</span><br><span class="line"></span><br><span class="line">// 设置每列的值</span><br><span class="line">rs.updateInt("id", 1);</span><br><span class="line">rs.updateString("name", "John");</span><br><span class="line">rs.updateInt("age", 20);</span><br><span class="line"></span><br><span class="line">// 将新的行插入到结果集中</span><br><span class="line">rs.insertRow();</span><br></pre></td></tr></tbody></table></figure>
<p>在上述示例中，我们首先创建了一个可更新结果集，并执行了查询操作。然后，使用 <code>moveToInsertRow()</code> 方法将光标移动到插入行的位置，接下来使用 <code>updateXXX()</code> 方法设置每列的值。最后，调用 <code>insertRow()</code> 方法将新的行插入到结果集中。</p>
<p>注意，使用可更新结果集时，需要确保数据库表具有适当的权限和支持，且在对结果集进行修改之后，要调用 <code>updateRow()</code> 方法来提交更改到数据库。</p>
<p><code>moveToInsertRow()</code> 方法允许在可更新结果集中移动到特定的插入行位置，并在此位置上进行新行的插入操作。</p>
<h1 id="事务"><a href="#事务" class="headerlink" title="事务"></a>事务</h1><p>可以将一组SQL语句构建成一个<strong>事务</strong>。当所有语句都顺利执行后，事务可以被<strong>提交（COMMIT）</strong>。如果其中某个语句遇到错误，那么事务将被<strong>回滚（ROLLBACK）</strong>，撤销遇到错误前的所有操作，就像没有任何语句被执行过一样。</p>
<p>如果将更新语句组合成一个事务，那么事务要么成功地执行所有操作并提交，要么在中间某个位置发生错误，发生错误后，需要执行回<strong>回滚（ROLLBACK）</strong>操作，数据库将自动撤销自上次提交事务以来的所有更新操作产生的影响。</p>
<h2 id="用JDBC对事务编程"><a href="#用JDBC对事务编程" class="headerlink" title="用JDBC对事务编程"></a>用JDBC对事务编程</h2><p>在Java中，可以使用以下步骤来执行事务：</p>
<ol>
<li>获取数据库连接，并将其设置为手动提交模式：<code>connection.setAutoCommit(false)</code>。</li>
<li>获取语句对象<code>Statement</code>。</li>
<li>在事务中执行数据库操作，例如插入、更新或删除数据。</li>
<li>如果所有的数据库操作都成功完成，调用 <code>connection.commit()</code> 提交事务，将修改持久化到数据库中。</li>
<li>如果出现错误或某个操作失败，调用 <code>connection.rollback()</code> 回滚事务，撤销之前的所有修改。</li>
<li>最后，关闭数据库连接。</li>
</ol>
<p>以下是一个简单的示例：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">try</span> {</span><br><span class="line">    <span class="comment">// 获取数据库连接</span></span><br><span class="line">    <span class="type">Connection</span> <span class="variable">connection</span> <span class="operator">=</span> DriverManager.getConnection(<span class="string">"jdbc:mysql://localhost:3306/mydatabase"</span>, <span class="string">"username"</span>, <span class="string">"password"</span>);</span><br><span class="line">    </span><br><span class="line">    <span class="comment">// 获取语句对象</span></span><br><span class="line">    <span class="type">Statement</span> <span class="variable">statement</span> <span class="operator">=</span> connection.createStatement();</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 设置手动提交模式</span></span><br><span class="line">    connection.setAutoCommit(<span class="literal">false</span>);</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 执行数据库操作</span></span><br><span class="line">    <span class="comment">// ...</span></span><br><span class="line">    statement.executeUpdate(command_1);</span><br><span class="line">    statement.executeUpdate(command_2);</span><br><span class="line">    statement.executeUpdate(command_3);</span><br><span class="line">	<span class="comment">// ...</span></span><br><span class="line">    <span class="comment">// 提交事务</span></span><br><span class="line">    connection.commit();</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 关闭数据库连接</span></span><br><span class="line">    connection.close();</span><br><span class="line">} <span class="keyword">catch</span> (SQLException e) {</span><br><span class="line">    <span class="comment">// 回滚事务</span></span><br><span class="line">    connection.rollback();</span><br><span class="line">    e.printStackTrace();</span><br><span class="line">}</span><br><span class="line"></span><br></pre></td></tr></tbody></table></figure>
<h2 id="批量更新"><a href="#批量更新" class="headerlink" title="批量更新"></a>批量更新</h2><p>批量更新（Batch Update）是一种在Java数据库编程中用于一次性执行多个数据库更新操作的机制，从而提高性能和效率。在使用批量更新时，一个语句序列作为一批操作将同时被收集和提交。</p>
<p>在Java中，可以使用以下步骤执行批量更新：</p>
<ol>
<li>获取数据库连接。</li>
<li>创建一个 <code>Statement</code> 或 <code>PreparedStatement</code> 对象。</li>
<li>将要执行的更新操作添加到批量更新中，可以使用<code>addBatch()</code>方法将每个更新操作添加到批量更新中。</li>
<li>执行批量更新，使用<code>executeBatch()</code>方法执行批量更新操作，该方法将为所有已提交的语句返回一个受影响行数的数组。</li>
<li>获取更新操作的结果，可以使用<code>getUpdateCount()</code>方法获取每个更新操作的影响行数，或者使用<code>getUpdateCounts()</code>方法获取整个批量更新操作的影响行数。</li>
<li>关闭相关的对象和数据库连接。</li>
</ol>
<p>以下是一个简单的示例：</p>
<figure class="highlight java"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">try</span> {</span><br><span class="line">    <span class="comment">// 获取数据库连接</span></span><br><span class="line">    <span class="type">Connection</span> <span class="variable">connection</span> <span class="operator">=</span> DriverManager.getConnection(<span class="string">"jdbc:mysql://localhost:3306/mydatabase"</span>, <span class="string">"username"</span>, <span class="string">"password"</span>);</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 创建 PreparedStatement 对象</span></span><br><span class="line">    <span class="type">PreparedStatement</span> <span class="variable">statement</span> <span class="operator">=</span> connection.prepareStatement(<span class="string">"UPDATE students SET age = ? WHERE id = ?"</span>);</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 添加更新操作到批量更新中</span></span><br><span class="line">    statement.setInt(<span class="number">1</span>, <span class="number">20</span>);</span><br><span class="line">    statement.setInt(<span class="number">2</span>, <span class="number">1</span>);</span><br><span class="line">    statement.addBatch();</span><br><span class="line"></span><br><span class="line">    statement.setInt(<span class="number">1</span>, <span class="number">21</span>);</span><br><span class="line">    statement.setInt(<span class="number">2</span>, <span class="number">2</span>);</span><br><span class="line">    statement.addBatch();</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 执行批量更新</span></span><br><span class="line">    <span class="type">int</span>[] updateCounts = statement.executeBatch();</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 获取更新操作的结果</span></span><br><span class="line">    <span class="keyword">for</span> (<span class="type">int</span> count : updateCounts) {</span><br><span class="line">        System.out.println(<span class="string">"影响行数: "</span> + count);</span><br><span class="line">    }</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 关闭 Statement 和连接</span></span><br><span class="line">    statement.close();</span><br><span class="line">    connection.close();</span><br><span class="line">} <span class="keyword">catch</span> (SQLException e) {</span><br><span class="line">    e.printStackTrace();</span><br><span class="line">}</span><br><span class="line"></span><br></pre></td></tr></tbody></table></figure>

    </div>

    
    
    

    <footer class="post-footer">
          <div class="post-tags">
              <a href="/tags/Java%E5%9F%BA%E7%A1%80-%E6%95%B0%E6%8D%AE%E5%BA%93%E7%BC%96%E7%A8%8B-Java%E6%95%B0%E6%8D%AE%E5%BA%93%E7%BC%96%E7%A8%8B/" rel="tag"># -Java基础 -数据库编程 -Java数据库编程</a>
          </div>

        

          <div class="post-nav">
            <div class="post-nav-item">
                <a href="/2023/05/31/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%8E%9F%E7%90%86%EF%BC%9A%E5%B9%B6%E5%8F%91%E6%8E%A7%E5%88%B6/" rel="prev" title="数据库原理：并发控制">
                  <i class="fa fa-chevron-left"></i> 数据库原理：并发控制
                </a>
            </div>
            <div class="post-nav-item">
                <a href="/2023/06/03/%E8%AE%A1%E7%BB%84%EF%BC%9Acache%E7%9A%84%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5%E5%92%8C%E5%8E%9F%E7%90%86/" rel="next" title="">
                   <i class="fa fa-chevron-right"></i>
                </a>
            </div>
          </div>
    </footer>
  </article>
</div>






</div>
  </main>

  <footer class="footer">
    <div class="footer-inner">


<div class="copyright">
  &copy; 
  <span itemprop="copyrightYear">2023</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">鸣蜩十七</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.js.org/muse/" rel="noopener" target="_blank">NexT.Muse</a> 强力驱动
  </div>

    </div>
  </footer>

  
  <div class="toggle sidebar-toggle" role="button">
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
  </div>
  <div class="sidebar-dimmer"></div>
  <div class="back-to-top" role="button" aria-label="返回顶部">
    <i class="fa fa-arrow-up fa-lg"></i>
    <span>0%</span>
  </div>

<noscript>
  <div class="noscript-warning">Theme NexT works best with JavaScript enabled</div>
</noscript>


  
  <script src="https://cdnjs.cloudflare.com/ajax/libs/animejs/3.2.1/anime.min.js" integrity="sha256-XL2inqUJaslATFnHdJOi9GfQ60on8Wx1C2H8DYiN1xY=" crossorigin="anonymous"></script>
<script src="/js/comments.js"></script><script src="/js/utils.js"></script><script src="/js/motion.js"></script><script src="/js/schemes/muse.js"></script><script src="/js/next-boot.js"></script>

  




  




  

  <script class="next-config" data-name="enableMath" type="application/json">true</script><script class="next-config" data-name="mathjax" type="application/json">{"enable":true,"tags":"none","js":{"url":"https://cdnjs.cloudflare.com/ajax/libs/mathjax/3.2.2/es5/tex-mml-chtml.js","integrity":"sha256-MASABpB4tYktI2Oitl4t+78w/lyA+D7b/s9GEP0JOGI="}}</script>
<script src="/js/third-party/math/mathjax.js"></script>



</body>
</html>
